<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE>FileHelpers Library - Marcos Meli</TITLE>
<LINK href="/favicon.ico" rel="SHORTCUT ICON"><LINK 
href="global.css" type=text/css 
rel=stylesheet>
<link type="text/css" rel="stylesheet" href="SyntaxHighlighter.css"></link>

<meta NAME="Description"        content="An easy to use .NET library to read/write data from fixed length or delimited files or streams. It has support to import/export data from different data sources." />
<meta name="Author"             content="Marcos Meli" />
<meta NAME="keywords" Content="filehelpers,CSV,Marcos_Meli,C#,free,opensource,Devoo.net,Delimited Files,Import/Export">
<meta NAME="Copyright" CONTENT="Copyright 2005-07 to Marcos Meli - www.Devoo.net. All Rights Reserved.">

</HEAD>
<BODY   text=black vLink=purple aLink=red link=blue 
bgColor=white leftMargin=0 topMargin=0>

		{$HEADER$}


<table width="100%" cellpadding="5" cellspacing="5">
<tr><td><div id=contentdiv>  <h2>Excel DataLink Example </h2>
      <blockquote>
        <p>This example shows how to enable the two directional communication between an Excel and a Text File. </p>
      </blockquote>
      <h2>The Problem </h2>
  <blockquote>
          <p>If you want to transfer data from a Data Source to a file or vice versa, you must use the <a href="FileHelpers.DataLink.IDataStorage.html">IDataStorage</a> interface. </p>
          <p>For example if you have the following table (from the Northwind DB)</p>
          <p><img src="orders1.png" width="652" height="374"></p>
          <p>&nbsp;</p>
        </blockquote>
        <h2> To use  Delimited Format</h2>
        <blockquote>
          <p>You must define:</p>
          <blockquote>
<textarea name="code" class="c#">
[DelimitedRecord(&quot;|&quot;)]
public class OrdersVerticalBar
{
    public int OrderID;
    public string CustomerID;
    public int EmployeeID; 
    public DateTime OrderDate;
    public DateTime RequiredDate;
    
    [FieldNullValue(typeof(DateTime), &quot;2005-1-1&quot;)]
    public DateTime ShippedDate; 
    public int ShipVia; 
    public decimal Freight;
}
</textarea>
          </blockquote>
        </blockquote>
        <h2> To use FixedLength Format</h2>
        <blockquote>
          <p>You must define:</p>
          <blockquote>
<textarea name="code" class="c#">
[FixedLengthRecord]
public class OrdersFixed
{
   [FieldFixedLength(7)]
   public int OrderID;
   
   [FieldFixedLength(12)]
   public string CustomerID;

   [FieldFixedLength(3)]
   public int EmployeeID;

   [FieldFixedLength(10)]
   public DateTime OrderDate;

   [FieldFixedLength(10)]
   public DateTime RequiredDate;

   [FieldFixedLength(10)]
   [FieldNullValue(typeof(DateTime), &quot;2005-1-1&quot;)]
   public DateTime ShippedDate;

   [FieldFixedLength(3)]
   public int ShipVia;

   [FieldFixedLength(10)]
   public decimal Freight;

}
</textarea>
       </blockquote>
        </blockquote>
        <h2>The DataLinkClass</h2>
        <p>How you can see in the <a href="class_diagram.html">Class Diagram</a> you must inherit from <a href="FileHelpers.DataLink.AccessStorage.html">AccessStorage</a> or <a href="FileHelpers.DataLink.SqlServerStorage.html">SqlServerStorage </a> to finish the DataLink.</p>
        <p>In this example we use the first one, and the resulting code is:</p>
        <blockquote>
<textarea name="code" class="c#">
public class OrdersLinkProvider : AccessStorage
{
    public override Type RecordType
    {
        get { return typeof (OrdersFixed); }
    }

    protected override string GetSelectSql()
    {
        return "SELECT * FROM Orders";
    }

    protected override object FillRecord(object[] fields)
    {
        OrdersFixed record = new OrdersFixed();

        record.OrderID = (int) fields[0];
        record.CustomerID = (string) fields[1];
        record.EmployeeID = (int) fields[2];
        record.OrderDate = (DateTime) fields[3];
        record.RequiredDate = (DateTime) fields[4];
        if (fields[5] != DBNull.Value)
            record.ShippedDate = (DateTime) fields[5];
        else
            record.ShippedDate = DateTime.MinValue;
        record.ShipVia = (int) fields[6];
        record.Freight = (decimal) fields[7];

        return record;
    }

    protected override string GetInsertSql(object record)
    {
        OrdersFixed obj = (OrdersFixed) record;

        return String.Format("INSERT INTO Orders &quot; +
           &quot; (CustomerID, EmployeeID, Freight, OrderDate, OrderID, RequiredDate, ShippedDate, ShipVia) " + 
           &quot;  VALUES ( \"{0}\" , \"{1}\" , \"{2}\" , \"{3}\" , \"{4}\" , \"{5}\" , \"{6}\" , \"{7}\"  ) ",
           obj.CustomerID,
           obj.EmployeeID,
           obj.Freight,
           obj.OrderDate,
           obj.OrderID,
           obj.RequiredDate,
           obj.ShippedDate,
           obj.ShipVia);
    
    }

    private string mAccessFileName = @"..\data\TestData.mdb";

    public override string MdbFileName
    {
        get { return mAccessFileName; }
    }

    public OrdersLinkProvider(string fileName)
    {
        mAccessFileName = fileName;
    }

}
</textarea>
        </blockquote>
        <blockquote>
          <blockquote>&nbsp;</blockquote>
        </blockquote>
        <h2>Sample output/input to the DataLinkEngine </h2>
        <blockquote>
          <blockquote>
<textarea name="code" class="c#:nogutter:nocontrols">
10248|VINET|5|04071996|01081996|16071996|3|32.38
10249|TOMSP|6|05071996|16081996|10071996|1|11.61
10250|HANAR|4|08071996|05081996|12071996|2|65.83
10251|VICTE|3|08071996|05081996|15071996|1|41.34
10252|SUPRD|4|09071996|06081996|11071996|2|51.3
10253|HANAR|3|10071996|24071996|16071996|2|58.17
10254|CHOPS|5|11071996|08081996|23071996|2|22.98
10255|RICSU|9|12071996|09081996|15071996|3|148.33
10256|WELLI|3|15071996|12081996|17071996|2|13.97
10257|HILAA|4|16071996|13081996|22071996|3|81.91
10258|ERNSH|1|17071996|14081996|23071996|1|140.51
10259|CENTC|4|18071996|15081996|25071996|3|3.25
</textarea>
          </blockquote>
        </blockquote>
        <h2>Finally Call to the DataLinkEngine </h2>
        <blockquote>
          <p>Finally you must to instantiate a DataLInkEngine and Read/Write files:</p>
          <blockquote>
<textarea name="code" class="c#">
DataLinkEngine mLinkEngine = new DataLinkEngine(new OrdersFixedLinkProvider());

// To extract from the data source to a file write:
mLinkEngine.ExtractToFile(&quot;tempout.txt&quot;);

// To insert from a file to the data source:
mLinkEngine.InsertFromFile(&quot;tempin.txt&quot;);
</textarea>
          </blockquote>
        </blockquote>
        <h2>Remarks</h2>
        <ul>
          <li>The right way to generate this classes is using the  <a href="http://www.ericjsmith.net/codesmith/">Code Smith Templates</a>.        </li>
        </ul>
		{$FOOTER$}
</tr>
</table>


</BODY></HTML>
